<?php

class StudentTransfer_Model_Core
{

  /**
     * 取出學生異動資料
     * @return unknown_type
     */
    public function getTransferDataByid($sn)
    {


        $q = Doctrine_Query::create()
        ->select('a.name, a.identification_number, a.sex,
          b.student_id AS student_id, b.grade AS grade, b.class_sn AS class_sn, b.number AS number,
          c.sn AS sn, c.year AS year, c.semester AS semester, c.causation_kind AS causation_kind, c.county AS county,
          c.school_name AS school_name, c.school_id AS school_id, c.date AS date, c.approve_organ AS approve_organ
          , c.causation AS causation, c.approve_date AS approve_date, c.approve_word AS approve_word,
          c.approve_number AS approve_number, c.teacher_sn AS teacher_sn,c.update_time AS update_time,c.student_sn AS student_sn
          ')
        ->from('Student_Model_Student a')
        ->leftJoin('a.Student_Model_StudentSemester  b')
        ->leftJoin('a.Student_Model_StudentTransfer c')
        ->where('b.year=c.year AND b.semester=c.semester')
        ->andWhere('c.sn=?',$sn)
        ;

        $res = $q->fetchOne(null,Doctrine_Core::HYDRATE_ARRAY);

       return $res;
    }

  public function getStudentAction()
    {
        $this->getHelper('layout')->disableLayout();
        $request = $this->getRequest();
        if ($request->getParam('year_semester')) {
            $arr = explode("-",$request->getParam('year_semester'));
            $year = (int)$arr[0];
            $semester =(int) $arr[1];
        }
        else {
            $year = $this->view->Semester['year'];
            $semester = $this->view->Semester['semester'];
        }
        $kind = $request->getParam('kind');
        $q = Doctrine_Query::create()
        ->select('a.*, b.name AS name, b.identification_number AS dentification_number, b.sex AS sex,
          c.student_id AS student_id, c.grade AS grade, c.class_sn AS class_sn, c.number AS number')
        ->from('Student_Model_StudentTransfer a ')
        ->leftJoin('a.Student_Model_Student  b')
        ->leftJoin('b.Student_Model_StudentSemester c')
        ->where('a.year=c.year AND a.semester=c.semester')
        ->andWhere('c.year=?', $year)
        ->andWhere('c.semester=?',$semester);
        switch ($kind) {
            case 'out':
                $q->andWhereIn('a.causation_kind',array(
                Student_Model_StudentTransfer::ABROAD,
                Student_Model_StudentTransfer::REDEPLOYMENT,
                Student_Model_StudentTransfer::REDEPLOYMENT,
                ));
                $tpl = 'moveout/getoutstudent';
                break;
            case 'in' :
                $q->andWhere('a.causation_kind=?',Student_Model_StudentTransfer::MOVE_IN);
                $tpl = 'movein/getinstudent';
                break;

            case 'rein':
                $q->andWhereIn('a.causation_kind',array(
                Student_Model_StudentTransfer::DROPOUTS_RETURN,
                Student_Model_StudentTransfer::LEAVE_RETURN,
                Student_Model_StudentTransfer::TRANSFER_RETURN
                ));
                $tpl = 'rein/getreinstudent';
                break;

            case 'demove':
                $q->andWhereIn('a.causation_kind',array(
                Student_Model_StudentTransfer::UPGRADE,
                Student_Model_StudentTransfer::DOWNGRADE
                ));
                $tpl = 'demove/getstudent';
                break;
        }

        $q->orderBy('a.date DESC');

        $res = $q->fetchArray();
        //print_r($res);
        $this->view->data = $res;
        $this->render($tpl);
    }

}